PGCM 模块二 优化实验
设置操作系统参数，要求如下：
- 修改磁盘预读大小为8192KB
/sbin/blockdev --setra 8192 /dev/sda
/sbin/blockdev --getra /dev/sda
1、设置数据库参数，要求如下：
- 可以使用pg_stat_statements去查询运行时间长的SQL语句。
vi $PGDATA/postgresql.conf
shared_preload_libraries='pg_stat_statements'
pg_stat_statements.max = 10000 # 在pg_stat_statements中最多保留多少条统计信息
pg_stat_statements.track = all # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = off # 是否跟踪非DML语句 (例如DDL，DCL)， on表示跟踪, off表示不跟踪
pg_stat_statements.save = on # 重启后是否保留统计信息
psql -U postgres -d postgres
select * from pg_stat_statements order by total_exec_time desc limit 5;
-
已知在本PostgreSQL实例中运行的SQL语句的最大大小为2KB，请设置postgreslq.conf配置参数，以记录到这些SQL语句的完整文本（即：不被截断）
-
要求在PostgreSQL运行日志中记录运行时间超过5秒的SQL语句及其执行计划，以便“对历史时段运行慢的SQL进行分析”。
PostgreSQL 安全管理 使用数据库日志审计
4) $PGDATA所在的磁盘是SSD（固态盘），请根据该情况修改PostgreSQL配置参数，以提高数据库的查询性能。
5) 按照操作系统的物理内存是2048MB来合适设置PostgreSQL的shared_buffers参数大小。
echo "random_page_cost = 1.1" >> $PGDATA/postgresql.conf
echo "shared_buffers ='512MB'" >> $PGDATA/postgresql.conf
安装并配置PGBouncer连接池，要求：
1) 安装在/home/pg15/pgbouncer这个目录中，pgbouncer.ini这个配置文件请放在/home/pg15/pgbouncer/这个目录下。
2) 配置PGBouncer使用的端口为5766，要求如下：
(1) PGBouncer中的数据连接名称请配置为appdb
(2) pgbouncer.log请放在/home/pg15/pgbouncer/这个目录下
(3) pgbouncer.pid请放在/home/pg15/pgbouncer/这个目录下
(4) 若非完成考题所需，其他配置请保持默认。
3) psql通过PGBouncer连接池以appuser用户连接到appdb数据库中，连接PGBouncer的端口是5766，请配置好.pgpass文件，以便使用psql免密登录。提示：.pgpass文件中的主机名请写局域网ip地址。
PostgreSQL 数据库性能 PgBouncer
1 源代码安装与配置
1、配置 PgBouncer 环境包。
su - root
yum install libevent-devel -y
2、上传 PgBouncer 源码到 /soft 文件夹解压 PgBouncer 源码。
su - pg15
pg_ctl start
cd /soft
tar -zxf pgbouncer-1.20.1.tar.gz
cd pgbouncer*/
4、配置编辑环境
./configure --prefix=/home/pg15/pgbouncer/ --with-libevent=libevent-prefi --with-pam --with-systemd
5、编译并安装
su - pg15
make -j 8
make install
//屏幕输出：
INSTALL pgbouncer /usr/local/bin
INSTALL README.md /usr/local/share/doc/pgbouncer
INSTALL NEWS.md /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer-minimal.ini /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.ini /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.service /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.socket /usr/local/share/doc/pgbouncer
INSTALL etc/userlist.txt /usr/local/share/doc/pgbouncer
INSTALL doc/pgbouncer.1 /usr/local/share/man/man1
INSTALL doc/pgbouncer.5 /usr/local/share/man/man5
1.1 配置文件相关路径
su - pg15
cp /home/pg15/pgbouncer/share/doc/pgbouncer/pgbouncer.ini /home/pg15/pgbouncer/pgbouncer.ini
cp /home/pg15/pgbouncer/share/doc/pgbouncer/userlist.txt /home/pg15/pgbouncer/userlist.txt
cp /soft/pgbouncer-1.20.1/etc/mkauth.py /home/pg15/pgbouncer/mkauth.py
1.2 配置 pgbouncer.ini
vi /home/pg15/pgbouncer/pgbouncer.ini
--------------------input------------------------------
;;;
;;; PgBouncer configuration file
;;;
;; database name = connect string
;;
;; connect string params:
;; dbname= host= port= user= password= auth_user=
;; client_encoding= datestyle= timezone=
;; pool_size= reserve_pool= max_db_connections=
;; pool_mode= connect_query= application_name=
[databases]
appdb = host=127.0.0.1 port=5666 user=appuser dbname=appdb
[pgbouncer]
logfile = /home/pg15/pgbouncer/pgbouncer.log
pidfile = /home/pg15/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5766
auth_type = md5
max_client_conn =150
auth_file = /home/pg15/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
1.3 配置 userlist.txt
vi /home/pg15/pgbouncer/userlist.txt
"appuser" "1qaz@WSX"
1.4 启动 pgbouncer 服务
cd /home/pg15/pgbouncer/
bin/pgbouncer pgbouncer.ini -d
1.5 验证是否能够登录
1、登录数据库。
psql -h 127.0.0.1 -p 5766 -U appuser -d appdb
passwd:1qaz@WSX
// 可以进行数据的正常操作
2、登录 pgbouncer 虚拟库。
psql -p 5766 -d pgbouncer -U postgres -h 127.0.0.1
// 对pgbouncer 虚拟机配置。
show help;
show nodes;
show pools;
1SQL优化
1) 以appuser登录，将目录下的t_hash.csv文件使用\copy命令导入到appdb数据库的appuser.t_hash表中,appuser.t_hash表的表结构如下：
id integer类型
md5 text类型
psql -U appuser -d appdb
CREATE TABLE appuser.t_hash(id integer,md5 text);
\copy appuser.t_hash FROM '/soft/t_hash.csv' csv;
2) 现有一个查询语句：
EXPLAIN SELECT * FROM appuser.t_hash Where md5 LIKE '%89f34%';
3) 请结合pg_trgm扩展进行建索引，以便如上语句的执行计划可以走索引扫描。
CREATE EXTENSION pg_trgm;
CREATE INDEX t_hash_md5_idx on appuser.t_hash USING gin(md5 gin_trgm_ops);